package util;

import java.sql.*;
import java.util.*;

/**
	организует доступ к таблицам заданной базы данных
*/

public class DBManager{
	private String DRIVER="com.mysql.jdbc.Driver";
	private String login;
	private String password;
	private Connection connection;
	private boolean error = false;
	
	/**
		передаем в параметры конструктора имя базы данных,
		логин и пароль к ней
	*/
	public DBManager(String dbName, String login, String password){
		this.login = login;
		this.password = password;
		
		try{	
			Class.forName(DRIVER);
			
			Properties properties=new Properties();
			properties.setProperty("user","root");
			properties.setProperty("password","root");
			properties.setProperty("useUnicode","true");
			properties.setProperty("characterEncoding","utf-8");
			connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/anagram", properties);
			
			Statement st = connection.createStatement();
			
		}catch(ClassNotFoundException cnfe){
			System.out.println("Error loading driver: "+cnfe);
			error = true;
		}catch(SQLException sqle){
			System.out.println("Error connecting: "+sqle);
			error = true;
		}
	}
	
	
	public static String getStringDate(java.util.Date date){
		if(date==null)
			return("\'0000-00-00 0:0:0\'");
			
		StringBuffer sb = new StringBuffer("\'"+date.getYear()+"-");
		sb.append(date.getMonth()+"-");
		sb.append(date.getDay()+" ");
		
		sb.append(date.getHours()+":");
		sb.append(date.getMinutes()+":");
		sb.append(date.getSeconds()+"\'");
		
		return sb.toString();
	}
	
	
	
	/**
		возвращает объект Statement, используемый для передачи sql-запросов
	*/
	public Connection getConnection(){
			return connection;
	}
	
	
	/**
		создает таблицу с именем tableName.
		fieldName - массив названий столбцов, fieldType - массив типов столбцов
	*/
	public void createTable(String tableName, String[] fieldName, String[] fieldType){
		Statement statement = null;
		
		try{ //удаляем ранее существующую таблицу, если она есть
			statement = getConnection().createStatement();
			statement.execute("DROP TABLE "+tableName);
		}catch(SQLException e){
		}
		
		StringBuilder query = new StringBuilder("CREATE TABLE "+tableName+" (");
		int i=0;
		for(; i<fieldName.length-1; i++){
			query.append(fieldName[i] + " " + fieldType[i]+", ");
		}
		query.append(fieldName[i] + " " + fieldType[i]+")");
		
		try{
			statement.execute(query.toString());
		}catch(SQLException e){
			System.out.println(e);
		}
	}
	
	
	public static void main(String args[]){
		DBManager dbManager = new DBManager("anagram","root","root");
	/*	String fieldName[] = {"userID", "lastName", "name", "middleName", "nickname", "password", "securityLevel", "birthDate", "registrationDate", "sex", "mail", ""};
		String fieldType[] = {
			"int NOT NULL AUTO_INCREMENT",
			"varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL",
			"varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL",
			"varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL",
			"varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL",
			"varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL",
			"int NOT NULL",
			"date NOT NULL",
			"datetime NOT NULL",
			"char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL", 
			"varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL",
			"primary key(userID)"
		};*/
		
	/*	String fieldName[] = {"articleID", "title", "body", "timeCreation", ""};
		String fieldType[] = {
			"int NOT NULL AUTO_INCREMENT",
			"varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL",
			"text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL",
			"datetime NOT NULL",
			"primary key(articleID)"
		};*/
		
		
		
		String fieldName[] = {"userID", "name", "lastName",  "results"};
		String fieldType[] = {
			"int NOT NULL",
			"varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL",
			"varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL",
			"int NOT NULL"
		};
		
	/*	String fieldName[] = {"menuID", "caption", ""};
		String fieldType[] = {
			"int NOT NULL AUTO_INCREMENT",
			"varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL",
			"primary key(menuID)"
		};*/
		
		
	/*	String fieldName[] = {"gameID", "userID", "startTime", "endTime", "results", "difficulty",""};
		String fieldType[] = {
			"int NOT NULL AUTO_INCREMENT",
			"int NOT NULL",
			"datetime NOT NULL",
			"datetime NOT NULL",
			"int NOT NULL",
			"int NOT NULL",
			"primary key(gameID)"
		};*/
		
		
	/*	String fieldName[] = {"menuID", "articleID"};
		String fieldType[] = {
			"int NOT NULL ",
			"int NOT NULL "
		};*/
		
		
	/*	String fieldName[] = {"roleID", "name", ""};
		String fieldType[] = {"int NOT NULL AUTO_INCREMENT",
			"varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL",
			"primary key(roleID)"
		};*/
		
	/*	String fieldName[] = {"securityAccessID", "roleID", "actionID", ""};
		String fieldType[] = {"int NOT NULL AUTO_INCREMENT",
			"int NOT NULL",
			"int NOT NULL",			
			"primary key(securityAccessID)"
		};*/
		
	/*	String fieldName[] = {"securityActionID", "name", ""};
		String fieldType[] = {"int NOT NULL AUTO_INCREMENT",
			"varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL",	
			"primary key(securityActionID)"
		};*/
		
		dbManager.createTable("buf_game_results", fieldName, fieldType);
		System.out.println("!");
	}
	
}